﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;
using JKF.DataModule.IDAL;
using JKF.Substructure;
using System.Data.SqlClient;//请先添加引用
namespace JKF.DataModule.SqlServerDAL
{
    public partial class dalSWING_CARD : IdalSWING_CARD
    {
        private const string FieldShow = "[ID],[POINT_KEY],[CUSTOMER_KEY],TRY_TIME,[TYPE],[SHOP_CODE],[STATUS],[USER_CREATE],[CARD_NO],[NAME] ,[MOBILE],[ACCOUNT] ,[POINT_VALUE]";
        private const string FieldOrder = "TRY_TIME DESC ";
        private const string FieldKey = "ID";

        /// <summary>
        /// 
        /// </summary>
        /// <param name="searchItem"></param>
        /// <param name="matchWay"></param>
        /// <param name="keyword"></param>
        /// <param name="dateStart"></param>
        /// <param name="dateEnd"></param>
        /// <param name="ownShop"></param>
        /// <param name="userKey"></param>
        /// <param name="pageCurrent"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageCount"></param>
        /// <param name="rowCount"></param>
        /// <returns></returns>
        public DataTable GetSwingCard(string searchItem, string matchWay, string keyword, string dateStart, string dateEnd,
         string ownShop,string userKey,int pageCurrent, int pageSize, ref int pageCount, ref int rowCount)
        {
            var sql = new StringBuilder(@"  1=1 and STATUS='1' ");
            sql.AppendFormat(" and exists(select shop_code from sys_user_shop  where V_SWING_CARD.shop_code=sys_user_shop.shop_code  and sys_user_shop.USER_KEY='{0}')",userKey);

            if (!string.IsNullOrEmpty(ownShop))
            {
                sql.AppendFormat(" and Shop_code in('{0}')", string.Join("','", ownShop.Split(',')));
            }

            if (!string.IsNullOrEmpty(searchItem) && !string.IsNullOrEmpty(keyword))
            {
                if (matchWay == "包含")
                {
                    sql.AppendFormat(" AND {0} LIKE '%{1}%' ", searchItem, keyword);
                }
                else
                {
                    sql.AppendFormat(" AND {0} = '{1}' ", searchItem, keyword);
                }
            }
            if (!string.IsNullOrEmpty(dateStart))
            {
                sql.AppendFormat(" and DATE_CREATE >'{0}'", dateStart);
            }
            if (!string.IsNullOrEmpty(dateEnd))
            {
                sql.AppendFormat(" and DATE_CREATE <'{0}'", dateEnd);
            }

            
            return DataPage.GetPageInfo("V_SWING_CARD", FieldKey, pageCurrent, pageSize, FieldShow, FieldOrder, sql.ToString(), ref pageCount, ref rowCount).Tables[0];
        }

        /// <summary>
        /// 获得顾客的卡号变更日志
        /// </summary>
        /// <param name="customerKey"></param>
        /// <param name="currentUser"></param>
        /// <returns></returns>
        public static DataTable GetCustomerCardChangeLog(string customerKey, string currentUser)
        {
            var sqlBuilder = new StringBuilder("SELECT CCC.ID,CCC.CUSTOMER_KEY,CCC.OLD_LEVEL,CCC.OLD_CARD,CCC.NEW_CARD,CCC.NEW_LEVEL,"
            +" CCC.DATE_CHANGE,CCC.DATE_CREATE,CUR.[NAME] AS CUSTOMER_NAME,ODL.[NAME] AS OLD_LEVEL_NAME,"
            +" NDL.[NAME] AS NEW_LEVEL_NAME,SU.REAL_NAME AS OPERATOR ");
            sqlBuilder.Append(" FROM CUSTOMER_CARD_CHANGE ccc "
            +" INNER JOIN customer cur on cur.customer_key=ccc.customer_key "
            +" LEFT JOIN sys_user su on su.user_key=ccc.user_create "
            +" LEFT JOIN DIC_LEVEL ODL ON ODL.LEVEL_CODE=CCC.OLD_LEVEL "
            +" LEFT JOIN DIC_LEVEL NDL ON NDL.LEVEL_CODE=CCC.OLD_LEVEL ");
            sqlBuilder.AppendFormat(" WHERE CUR.SHOP_CODE IN (SELECT SHOP_CODE FROM SYS_USER_SHOP WHERE USER_KEY='{0}') AND CCC.CUSTOMER_KEY='{1}' "
            +" ORDER BY CCC.DATE_UPDATE DESC ",currentUser, customerKey);

            return DB.HR.ExecuteDataSet(CommandType.Text, sqlBuilder.ToString()).Tables[0];
        }
    }

}
